package com.awenhui.demo.core.utils.poi;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.jdbc.Work;

import javax.persistence.criteria.CriteriaBuilder;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

/**
 * 生成Excel
 * 1.根据LinkedHashMap生成Excel
 * 2.根据模板生成Excel(需定义头部)
 * 3.根据字段注解生成excel
 *
 * @author yuxf
 * @version 1.0
 * @date 2020/7/22 15:17
 */
public class ExcelUtils {

    /**
     * 获取Workbook-字段注解（ExcelHeader）定义头部
     *
     * @param clazz
     * @param list
     * @param headCellAction
     * @param bodyCellAction
     * @param <T>
     * @return
     */
    public static <T> Workbook getWorkbook(Class<T> clazz, List<T> list, Consumer<CellActionArgs> headCellAction, Consumer<CellActionArgs> bodyCellAction) throws Exception {
        Field[] fields = clazz.getDeclaredFields();
        //获取带注解ExcelHeader的字段
        List<Field> fieldList = Arrays.stream(fields).filter(r -> r.isAnnotationPresent(ExcelHeader.class)).collect(Collectors.toList());
        if (fieldList == null || fieldList.size() == 0) {
            throw new Exception("not found ExcelHeader annotation field");
        }
        //排序
        LinkedHashMap<String, String> headConfig = new LinkedHashMap<>();
        for (int i = 1; i <= fieldList.size(); i++) {
            for (int j = 0; j < fieldList.size(); j++) {
                Field field = fieldList.get(j);
                ExcelHeader headerAno = field.getAnnotation(ExcelHeader.class);
                if (headerAno.index() == i) {
                    headConfig.put(field.getName(), headerAno.value());
                }
            }
        }
        return getWorkbook(headConfig, list, headCellAction, bodyCellAction);
    }

    /**
     * 获取Workbook-LinkedHashMap定义头部
     *
     * @param <T>
     * @return
     */
    public static <T> Workbook getWorkbook(LinkedHashMap<String, String> headConfig, List<T> list, Consumer<CellActionArgs> headCellAction, Consumer<CellActionArgs> bodyCellAction) throws NoSuchFieldException, IllegalAccessException {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        Row row = sheet.createRow(0);
        int headIndex = 0;
        for (String key : headConfig.keySet()) {

            Cell cell = row.createCell(headIndex);
            cell.setCellValue(headConfig.get(key));
            ExeCellAction(headCellAction, workbook, sheet, row, key, headIndex, "java.lang.String");

            int length = 256;
            if (cell.getStringCellValue() != null) {
                length = cell.getStringCellValue().getBytes().length * 256 + 256;
            }
            sheet.setColumnWidth(headIndex, length);
            System.out.println("第"+headIndex+"列宽度为："+length/256);
            headIndex++;
        }
        return getWorkbook(workbook, sheet, row, Arrays.asList(headConfig.keySet().toArray(new String[0])), list, bodyCellAction);
    }

    /**
     * 获取Workbook-模板定义头部
     *
     * @param <T>
     * @return
     */
    public static <T> Workbook getWorkbook(String templatePath, List<String> fieldList, List<T> list, Consumer<CellActionArgs> headCellAction, Consumer<CellActionArgs> bodyCellAction) throws IOException, NoSuchFieldException, IllegalAccessException {
        Workbook workbook = new XSSFWorkbook(templatePath);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        for (int i = 0; i < fieldList.size(); i++) {
            ExeCellAction(headCellAction, workbook, sheet, row, fieldList.get(i), i, "java.lang.String");
        }
        return getWorkbook(workbook, sheet, row, fieldList, list, bodyCellAction);
    }

    /**
     * 保存excel到文件
     *
     * @param workbook
     * @param destFilePath 目标文件路径（含文件名.xlsx）
     */
    public static void saveToFile(Workbook workbook, String destFilePath) throws IOException {
        FileOutputStream os = new FileOutputStream(destFilePath);
        workbook.write(os);
        os.close();
        System.out.println(destFilePath + "保存成功");
    }

    /**
     * 导出Excel文件到客户端
     *
     * @param workbook 表格数据封装类
     * @param response
     * @param fileName 文件名称
     * @throws Exception
     */
    public static void outEcxelFile(Workbook workbook, HttpServletResponse response, String fileName) throws Exception {
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            // 下面三行是关键代码，处理乱码问题
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String finalFileName = URLEncoder.encode(fileName, "UTF8");
            response.setHeader("Content-Disposition", "attachment; filename=" + finalFileName + ".xlsx");
            workbook.write(out);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param list
     * @param <T>
     * @param bodyCellAction 数据cell个性化操作,默认操作传null
     */
    private static <T> Workbook getWorkbook(Workbook workbook, Sheet sheet, Row rowHead, List<String> filedList, List<T> list, Consumer<CellActionArgs> bodyCellAction) throws NoSuchFieldException, IllegalAccessException {

        //获取数据列表
        List<Object[]> listData = getDataList(filedList, list);
        String[] types = new String[filedList.size()];
        if (list.get(0) != null) {
            int typeIndex = 0;
            for (String key : filedList) {
                types[typeIndex] = list.get(0).getClass().getDeclaredField(key).getType().getName();
                typeIndex++;
            }
        }
        //生成数据行
        int rowDataIndex = 1;
        HashMap<Integer, Integer> maxWidth = new HashMap<>();
        for (Object[] data : listData) {
            Row rowData = sheet.createRow(rowDataIndex);
            for (int i = 0; i < data.length; i++) {
                Cell cell = rowData.createCell(i);
                //设置单元格数据
                setCellValue(cell, data[i], types[i]);
                //个性化处理每个单元格
                ExeCellAction(bodyCellAction, workbook, sheet, rowData, data[i], i, types[i]);
                //存储最大列宽
                int length = 0;

                if (cell.getCellType()== CellType.STRING&&cell.getStringCellValue() != null) {
                    length = cell.getStringCellValue().getBytes().length * 256;
                }
                //这里把宽度最大限制到15000
                length = length > 50 * 256 ? 50 * 256 : length;
                maxWidth.put(i, Math.max(length, maxWidth.get(i) == null ? 3 * 256 : maxWidth.get(i)));
            }
            rowDataIndex++;
        }
        //设置列宽
        for (HashMap.Entry<Integer, Integer> entry : maxWidth.entrySet()) {
          if(sheet.getColumnWidth(entry.getKey())<entry.getValue())
          {
              sheet.setColumnWidth(entry.getKey(), entry.getValue());
          }

            System.out.println("设置第"+entry.getKey()+"的宽度为："+entry.getValue()/256);
        }
        return workbook;
    }


    /**
     * 执行自定义的cell处理
     *
     * @param workbook
     * @param sheet
     * @param row
     * @param data
     * @param cellIndex
     * @param typeName
     */
    private static void ExeCellAction(Consumer<CellActionArgs> action, Workbook workbook, Sheet sheet, Row row, Object data, int cellIndex, String typeName) {
        if (action == null) return;
        ;
        CellActionArgs args = new CellActionArgs();
        args.setWorkbook(workbook);
        args.setSheet(sheet);
        args.setRow(row);
        args.setData(data);
        args.setCellIndex(cellIndex);
        args.setTypeName(typeName);
        action.accept(args);
    }

    /**
     * 获取数据列表
     *
     * @return
     */
    private static <T> List<Object[]> getDataList(List<String> fieldList, List<T> list) throws NoSuchFieldException, IllegalAccessException {
        List<Object[]> listData = new ArrayList<>();
        for (T item : list) {
            Object[] values = new Object[fieldList.size()];
            int valueIndex = 0;
            for (String key : fieldList) {
                Field field = item.getClass().getDeclaredField(key);
                // 设置访问权限为true
                field.setAccessible(true);
                values[valueIndex] = field.get(item);

                valueIndex++;
            }
            listData.add(values);
        }
        ;
        return listData;
    }


    /**
     * 设置单元格数据
     *
     * @param data
     * @param typeName
     */
    static void setCellValue(Cell cell, Object data, String typeName) {

        if (data != null) {
            if (typeName.equals("java.lang.String")) cell.setCellValue((String) data);
            if (typeName.equals("java.lang.Integer") || typeName.equals("int")) {
                if (data != null) cell.setCellValue((int) data);
            }
            if (typeName.equals("java.lang.Long") || typeName.equals("long")) {
                if (data != null) cell.setCellValue((long) data);
            }
            if (typeName.equals("java.lang.Double") || typeName.equals("double")) {
                if (data != null) cell.setCellValue((double) data);
            }
            if (typeName.equals("java.lang.Float") || typeName.equals("float")) {
                if (data != null) cell.setCellValue((float) data);
            }
            if (typeName.equals("java.lang.Boolean") || typeName.equals("boolean")) {
                if (data != null) cell.setCellValue((boolean) data);
            }
            if (typeName.equals("java.util.Date")) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                String dateStr = dateFormat.format((Date) data);
                cell.setCellValue(dateStr);
            }
        }

    }

}
